var VersionId = 1000;
var CurrentSyncUrlId = 1100;
var CurrentVersion = "1.3";

var CheckDataVersionTableExistsSql = "SELECT count(*) AS cnt FROM sqlite_master WHERE type='table' AND name='SysVersion';";
var GetSysVersionEntrySql = "SELECT Val FROM SysVersion WHERE Id = ?";
var DropDataVersionSql = "DROP TABLE IF EXISTS SysVersion";
var CreateDataVersionSql = "CREATE TABLE IF NOT EXISTS SysVersion (Id INTEGER, Val TEXT);";
var DeleteSysVersionEntrySql = "DELETE FROM SysVersion WHERE Id = ?";
var InsertSysVersionEntrySql = "INSERT INTO SysVersion (Id, Val) VALUES (?,?)";


/* VersionVal = "1.0" */
var CreateDataTableCompanySql = "CREATE TABLE IF NOT EXISTS Company (AndroidId INTEGER NOT NULL PRIMARY KEY,Id INTEGER, CompanyName TEXT);";
var CreateDataTableCompanyAddressSql = "CREATE TABLE IF NOT EXISTS CompanyAddress (AndroidId INTEGER NOT NULL PRIMARY KEY,Id INTEGER, Address TEXT, PostalCode TEXT, AddressType TEXT, CompanyId INTEGER);";
var CreateDataTableCompanyEmailSql = "CREATE TABLE IF NOT EXISTS CompanyEmail (AndroidId INTEGER NOT NULL PRIMARY KEY,Id INTEGER, Email TEXT, CompanyId INTEGER, Name TEXT);";
var CreateDataTableCompanyContactNumberSql = "CREATE TABLE IF NOT EXISTS CompanyContactNumber "+
    "(AndroidId INTEGER NOT NULL PRIMARY KEY,Id INTEGER, ContactNumber TEXT, CompanyId INTEGER, ContactName TEXT);";
var CreateDataTableContactSql = "CREATE TABLE IF NOT EXISTS Contact (Id INTEGER, FirstName TEXT, MiddleName TEXT, LastName TEXT, OfficeNumber TEXT, Email TEXT, MobileNumber TEXT, CompanyId INTEGER);";
var CreateDataTableHelpdeskCaseSql = "CREATE TABLE IF NOT EXISTS HelpdeskCase (DeviceId TEXT, CaseId INTEGER NOT NULL PRIMARY KEY, " +
    "ContactId INTEGER, CaseOpenDate TEXT, CaseCloseDate TEXT, CaseNumber TEXT, EngineerUserId INTEGER, "+
    "IssueDetail TEXT, LastModifiedDate TEXT, Status INTEGER);";
var CreateDataTableUserAccountSql = "CREATE TABLE IF NOT EXISTS UserAccount (UserId INTEGER, FirstName TEXT, MiddleName TEXT, LastName TEXT);";
var CreateDataTableHelpdeskCaseResolutionSql = "CREATE TABLE IF NOT EXISTS HelpdeskCaseResolution (ResolutionId INTEGER NOT NULL PRIMARY KEY, " +
    "DeviceId INTEGER, CaseId INTEGER, Notes TEXT, NotesType INTEGER, ResolutionDate TEXT, UserId INTEGER, LastModifiedDate TEXT)";

var DropDataTableCompanySql = "DROP TABLE IF EXISTS Company";
var DropDataTableCompanyAddressSql = "DROP TABLE IF EXISTS CompanyAddress";
var DropDataTableCompanyEmailSql = "DROP TABLE IF EXISTS CompanyEmail";
var DropDataTableCompanyContactNumberSql = "DROP TABLE IF EXISTS CompanyContactNumber";
var DropDataTableContactSql = "DROP TABLE IF EXISTS Contact";
var DropDataTableHelpdeskCaseSql = "DROP TABLE IF EXISTS HelpdeskCase";
var DropDataTableUserAccountSql = "DROP TABLE IF EXISTS UserAccount";
var DropDataTableHelpdeskCaseResolutionSql = "DROP TABLE IF EXISTS HelpdeskCaseResolution";


/* VersionVal = "1.1" */
var UpdateSysVersionEntrySql = "UPDATE SysVersion SET Val=? WHERE Id=?;";

var AlterDataTableCompanyDeletedSql="ALTER TABLE Company ADD Deleted Text;";
var AlterDataTableCompanyOperateDateSql="ALTER TABLE Company ADD OperateDate Text;";
var AlterDataTableCompanyAddressDeletedSql= "ALTER TABLE CompanyAddress ADD Deleted Text;";
var AlterDataTableCompanyAddressOperateDateSql= "ALTER TABLE CompanyAddress ADD OperateDate Text;";
var AlterDataTableCompanyEmailDeletedSql ="ALTER TABLE CompanyEmail ADD Deleted Text;";
var AlterDataTableCompanyEmailOperateDateSql ="ALTER TABLE CompanyEmail ADD OperateDate Text;";
var AlterDataTableCompanyContactNumberDeletedSql= "ALTER TABLE CompanyContactNumber ADD Deleted Text;";
var AlterDataTableCompanyContactNumberOperateDateSql= "ALTER TABLE CompanyContactNumber ADD OperateDate Text;";
var AlterDataTableHelpdeskCaseDeletedSql = "ALTER TABLE HelpdeskCase ADD Deleted Text;";
var AlterDataTableHelpdeskCaseOperateDateSql = "ALTER TABLE HelpdeskCase ADD OperateDate Text;";
var AlterDataTableUserAccountDeletedSql = "ALTER TABLE UserAccount ADD Deleted Text;";
var AlterDataTableUserAccountOperateDateSql = "ALTER TABLE UserAccount ADD OperateDate Text;";
var AlterDataTableHelpdeskCaseResolutionDeletedSql="ALTER TABLE HelpdeskCaseResolution ADD Deleted Text;";
var AlterDataTableHelpdeskCaseResolutionOperateDateSql="ALTER TABLE HelpdeskCaseResolution ADD OperateDate Text;";
var AlterDataTableHelpdeskCaseContactCaseIdSql="ALTER TABLE HelpdeskCase ADD ContactCaseId Text;";
var AlterDataTableCompanyRelationshipSql="Alter table Company add Relationship integer";
var AlterDataTableCompanyIndustrySql="Alter table Company add Industry integer";
var AlterDataTableCompanyStatusSql="Alter table Company add Status integer";
var AlterDataTableCompanyAccountManagerSql="Alter table Company add AccountManager integer";
var AlterDataTableCompanySourceSql="Alter table Company add Source integer";
var AlterDataTableEmployeeSql="Alter table Company add Employee integer";
var AlterDataTableWebsiteSql="Alter table Company add Website Text";
var AlterDataTableCompanyAndroidCompanyId="Alter table Company add AndroidCompanyId Text";
var AlterDataTableCompanyAddressAndroidCompanyId="Alter table CompanyAddress add AndroidCompanyId Text";
var AlterDataTableCompanyEmailAndroidCompanyId="Alter table CompanyEmail add AndroidCompanyId Text";
var AlterDataTableCompanyContactNumberAndroidCompanyId="Alter table CompanyContactNumber add AndroidCompanyId Text";
var AlterDataTableCompanyAddressCity="Alter table CompanyAddress add City Text";
var AlterDataTableCompanyAddressCountry="Alter table CompanyAddress add Country Text";

var CreateDataTableNewContactSql = "CREATE TABLE IF NOT EXISTS Contact (AndroidId INTEGER NOT NULL PRIMARY KEY,Id INTEGER, FirstName TEXT, MiddleName TEXT, "+
     "LastName TEXT,Designation TEXT,Classification TEXT,AccountManager INTEGER, OfficeNumber TEXT,FaxNumber TEXT,"+
      "Email TEXT,Notes TEXT,Salutation INTEGER,Gender INTEGER,Department TEXT,Section TEXT,CompanyId INTEGER, "+
      "MobileNumber TEXT,HomeNumber TEXT,AlternateEmail TEXT,Deleted Text,OperateDate Text,ContactCaseId Text,AndroidCompanyId Text);";
var CreateDataTableClassificationSql="Create Table if not exists Classification(Id integer,Name Text,Deleted Text,OperateDate Text);";
var CreateDataTableUpdatePropertySql="Create Table If not exists UpdateProperty(Id integer,AndroidUUID Text,LastUpdateTime Text);";
var CreateDataTableEmployeeSql="Create table if not exists Employee(Id integer ,Name Text)";
var CreateDataTableRelationshipSql = " Create Table if not exists Relationship(Id integer,Name Text)";
var CreateDataTableSourceSql = " Create Table if not exists Source(Id integer,Name Text)";
var CreateDataTableIndustrySql = " Create Table if not exists Industry(Id integer,Name Text)";



var DropDataTableClassificationSql="Drop table if exists Classification";

var InsertDataTableUpdatePropertySql="Insert into UpdateProperty(Id,AndroidUUID) values (?,?);";





var DBDefer;
var DBVersion = null;
var DBHandle = null;

function InitializeDatabase ()
{
    DBDefer = $.Deferred();
    var dbBase = GetDatabase();
    dbBase.transaction(CheckDataVersionTableExistsQuery, DetermineDataVersionError);
    return DBDefer.promise();
}

function CheckDataVersionTableExistsQuery (tx,results)
{
    tx.executeSql(CheckDataVersionTableExistsSql, [], CheckDataVersionTableExistsSuccess, DetermineDataVersionError);
}

function CheckDataVersionTableExistsSuccess (tx, results)
{
    if (results.rows.length <= 0){
        DBVersion = null;
        DBDefer.reject();
        return;
    }

    var tablecount = results.rows.item(0).cnt;
    if (tablecount > 0){
        // table exists
        CheckDataVersionQuery();
    }
    else{
        // table don't exist
        CreateDatabase ();
        CheckDataVersionQuery();
    }
}

function CheckDataVersionQuery ()
{
    var db = GetDatabase();
    db.transaction(function (tx) {
    tx.executeSql (GetSysVersionEntrySql, [VersionId],
        function (tx, results) {
            if (results.rows.length <= 0){
                DBVersion = null;
                DBDefer.reject();
                return;
            }
            DBVersion = results.rows.item(0).Val;
            ExecuteVersionFun(DBVersion);
            DBDefer.resolve();
        }, DetermineDataVersionError) ;
    });
}

function ExecuteVersionFun(version, innerDef)
{
    var IntVersion = 9;
    if (null != version && null != innerDef) {
        IntVersion = version;
    }else if(null != version){
        if (!isNaN(version)) {
            IntVersion = parseInt(parseFloat(version) * 10 + 1);
        }
    }

    if (IntVersion > 9) {
        eval("VersionFunction_" + IntVersion + "();");
    }
    var IntCurrentVersion = parseInt(parseFloat(CurrentVersion) * 10);
    if (IntVersion < IntCurrentVersion) {
        IntVersion = IntVersion + 1;
        ExecuteVersionFun(IntVersion, true);
    }
}


/*VersionVal = "1.1"*/
function VersionFunction_11()
{
    var db = GetDatabase();
    db.transaction(function (tx) {
        // update current version
        tx.executeSql(UpdateSysVersionEntrySql,["1.1",VersionId]);

        // Alter company tables
        tx.executeSql(AlterDataTableCompanyDeletedSql);
        tx.executeSql(AlterDataTableCompanyAddressDeletedSql);
        tx.executeSql(AlterDataTableCompanyContactNumberDeletedSql);
        tx.executeSql(AlterDataTableCompanyEmailDeletedSql);
        tx.executeSql(AlterDataTableCompanyOperateDateSql);
        tx.executeSql(AlterDataTableCompanyAddressOperateDateSql);
        tx.executeSql(AlterDataTableCompanyContactNumberOperateDateSql);
        tx.executeSql(AlterDataTableCompanyEmailOperateDateSql);
        tx.executeSql(AlterDataTableCompanyRelationshipSql);
        tx.executeSql(AlterDataTableCompanyIndustrySql);
        tx.executeSql(AlterDataTableCompanyStatusSql);
        tx.executeSql(AlterDataTableCompanyAccountManagerSql);
        tx.executeSql(AlterDataTableCompanySourceSql);
        tx.executeSql(AlterDataTableEmployeeSql);
        tx.executeSql(AlterDataTableWebsiteSql);
        tx.executeSql(AlterDataTableCompanyAndroidCompanyId);
        tx.executeSql(AlterDataTableCompanyAddressAndroidCompanyId);
        tx.executeSql(AlterDataTableCompanyEmailAndroidCompanyId);
        tx.executeSql(AlterDataTableCompanyContactNumberAndroidCompanyId);
        tx.executeSql(AlterDataTableCompanyAddressCity);
        tx.executeSql(AlterDataTableCompanyAddressCountry);

        // New contact table
        tx.executeSql(DropDataTableContactSql);
        tx.executeSql(CreateDataTableNewContactSql);

        // Alter user account table
        tx.executeSql(AlterDataTableUserAccountDeletedSql);
        tx.executeSql(AlterDataTableUserAccountOperateDateSql);

        // Alter helpdeskcase table
        tx.executeSql(AlterDataTableHelpdeskCaseDeletedSql);
        tx.executeSql(AlterDataTableHelpdeskCaseOperateDateSql);
        tx.executeSql(AlterDataTableHelpdeskCaseContactCaseIdSql);

        // Alter helpdesk resolution table
        tx.executeSql(AlterDataTableHelpdeskCaseResolutionDeletedSql);
        tx.executeSql(AlterDataTableHelpdeskCaseResolutionOperateDateSql);

        //create Classification table
        tx.executeSql(CreateDataTableClassificationSql);

        //create UpdateTime table
        var uuid=device.uuid;
        tx.executeSql(CreateDataTableUpdatePropertySql);
        tx.executeSql(InsertDataTableUpdatePropertySql,["1",uuid]);

        //create table Employee
        tx.executeSql(CreateDataTableEmployeeSql);

        //Create table Relationship
        tx.executeSql(CreateDataTableRelationshipSql);

        //Create table Source
        tx.executeSql(CreateDataTableSourceSql);

        //Create table Industry
        tx.executeSql(CreateDataTableIndustrySql);
    },DetermineDataVersionError);
}

function VersionFunction_12()
{

}
function VersionFunction_13()
{

}

function GetSysVersion (id)
{
    DBDefer = $.Deferred();

    var db = GetDatabase();
    db.transaction(function (tx) {
        tx.executeSql(GetSysVersionEntrySql, [id], function (tx, result) {
            if (result.rows.length > 0)
                DBDefer.resolve (result.rows.item(0).Val);
            else
                DBDefer.resolve (null);
        }, DetermineDataVersionError);
    }, DetermineDataVersionError);

    return DBDefer.promise();
}

function InsertSysVersion (id, val)
{
    DBDefer = $.Deferred();

    var db = GetDatabase();
    db.transaction(function (tx) {
        tx.executeSql(InsertSysVersionEntrySql, [id, val], function (tx, result) {
            DBDefer.resolve(true);
        },DetermineDataVersionError);
    }, DetermineDataVersionError);

    return DBDefer.promise();
}

function DeleteSysVersion (id)
{
    DBDefer = $.Deferred();

    var db = GetDatabase();
    db.transaction(function (tx) {
        tx.executeSql(DeleteSysVersionEntrySql, [id], function (tx, result) {
            DBDefer.resolve(true);
        },DetermineDataVersionError);
    }, DetermineDataVersionError);

    return DBDefer.promise();
}


function DetermineDataVersionError (tx, err)
{
    alert (tx.message);
    DBVersion = null;
    DBDefer.reject();
}

function GetDatabase ()
{
    if (DBHandle != null)
        return DBHandle;

    DBHandle = window.openDatabase("HelpDeskDB", "1.0", "HelpDeskDB", 5000000);
    return DBHandle;
}

function CreateDatabase ()
{
    var defer=$.Deferred();
    var db = GetDatabase();
    db.transaction(function (tx) {
        tx.executeSql(DropDataVersionSql);
        tx.executeSql(CreateDataVersionSql);

        // insert current version
        tx.executeSql(InsertSysVersionEntrySql,[VersionId, "1.0"]);

        // create company tables
        tx.executeSql(CreateDataTableCompanySql);
        tx.executeSql(CreateDataTableCompanyAddressSql);
        tx.executeSql(CreateDataTableCompanyContactNumberSql);
        tx.executeSql(CreateDataTableCompanyEmailSql);

        // create contact table
        tx.executeSql(CreateDataTableContactSql);

        // create helpdesk case table
        tx.executeSql(CreateDataTableHelpdeskCaseSql);

        // create user account table
        tx.executeSql(CreateDataTableUserAccountSql);

        // create helpdesk resolution table
        tx.executeSql(CreateDataTableHelpdeskCaseResolutionSql);

        //alert('Running in Test Mode. Data will be reinitialized.');
        DBDefer.resolve();
        defer.resolve(true);

    }, DetermineDataVersionError);
    return defer.promise();
}

function DBEmptyHandler (tx, results)
{
    return;
}

function InsertDummyData ()
{
    var dummy1 =
    {
        "Id" : "1",
        "CompanyName" : "Company 1",
        "Address" :
            [{
                "Id" : "1",
                "Address" : "This is a test address",
                "PostalCode" : "123456",
                "AddressType" : "SampleAddress",
                "CompanyId" : "1"
            },
                {
                    "Id" : "2",
                    "Address" : "This is a test address 2",
                    "PostalCode" : "123450",
                    "AddressType" : "SampleAddress 2",
                    "CompanyId" : "1"
                }],
        "Email" :
            [{
                "Id" : "1",
                "Email" : "test@company1.com",
                "CompanyId" : "1",
                "Name" : "Main Email"
            },
                {
                    "Id" : "2",
                    "Email" : "test2@company1.com",
                    "CompanyId" : "1",
                    "Name" : "Main Email2"
                }
            ],
        "ContactNumber" :
            [{
                "Id" : "1",
                "ContactNumber" : "01234567",
                "CompanyId" : "1",
                "ContactName" : "Tel"
            },
                {
                    "Id" : "2",
                    "ContactNumber" : "01234560",
                    "CompanyId" : "1",
                    "ContactName" : "Tel2"
                }
            ]
    };

    var dummy2 =
    {
        "Id" : "2",
        "CompanyName" : "Company 2",
        "Address" :
            [{
                "Id" : "10",
                "Address" : "This is a test address",
                "PostalCode" : "222222",
                "AddressType" : "SampleAddress",
                "CompanyId" : "2"
            }],
        "Email" :
            [{
                "Id" : "10",
                "Email" : "test@company2.com",
                "CompanyId" : "2",
                "Name" : "Main Email"
            }],
        "ContactNumber" :
            [{
                "Id" : "10",
                "ContactNumber" : "01234567",
                "CompanyId" : "2",
                "ContactName" : "Tel"
            }]
    };

    var contact1 =
    {
        "Id" : "1",
        "FirstName" : "Person",
        "MiddleName" : "",
        "LastName" : "1",
        "OfficeNumber" : "6123456",
        "Email" : "contact1@test.com",
        "MobileNumber" : "91234567",
        "CompanyId" : "1"
    };

    var contact2 =
    {
        "Id" : "2",
        "FirstName" : "Person",
        "MiddleName" : "",
        "LastName" : "2",
        "OfficeNumber" : "6123450",
        "Email" : "contact2@test.com",
        "MobileNumber" : "91234560",
        "CompanyId" : "1"
    };

    var contact3 =
    {
        "Id" : "3",
        "FirstName" : "Person",
        "MiddleName" : "",
        "LastName" : "3",
        "OfficeNumber" : "6123400",
        "Email" : "contact3@test.com",
        "MobileNumber" : "91234500",
        "CompanyId" : "2"
    };

    var case1 =
    {
        DeviceId : DeviceId,
        "ContactId" : "1",
        "CaseOpenDate" : "2000-01-01 09:00:00",
        "CaseCloseDate" : "",
        "CaseNumber" : "C30000001",
        "Status" : "0",
        "EngineerUserId" : "1",
        "IssueDetail" : "this is a sample issue",
        "LastModifiedDate" : "2000-01-02 10:00:00"
    };

}
function DropTables ()
{
    var db = GetDatabase();
    db.transaction (function (tx) {
        tx.executeSql(DropDataVersionSql);
        tx.executeSql(DropDataTableCompanySql);
        tx.executeSql(DropDataTableCompanyAddressSql);
        tx.executeSql(DropDataTableCompanyContactNumberSql);
        tx.executeSql(DropDataTableCompanyEmailSql);
        tx.executeSql(DropDataTableContactSql);
        tx.executeSql(DropDataTableHelpdeskCaseSql);
        tx.executeSql(DropDataTableUserAccountSql);
        tx.executeSql(DropDataTableHelpdeskCaseResolutionSql);
        //tx.executeSql(DropDataTableClassification);
    });
}